﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;
using System.Configuration;
using System.ComponentModel.DataAnnotations;
using TracNghiem.Models;
using System.Collections.ObjectModel;
using System.Web.Mvc;
using System.Data.OracleClient;

namespace TracNghiem.Models
{
    public class Test_QuestionModel
    {
        public string id { get; set; }
        public string name { get; set; }
        public string ghi_chu { get; set; }
        public string status { get; set; }
        public string ngay_tao { get; set; }
        public string ngay_ket_thuc { get; set; }
        public string phanloai_id { get; set; }
        public string cau_tl_id { get; set; }
        public string cau_tl_name { get; set; }
        public string trangthai_tl { get; set; }

        public string de_thi_id { get; set; }
        public string status_user { get; set; }
        public string kq { get; set; }
        public List<Test_QuestionModel> getQuestionRandom(string de_thi_id, string[] phanloai_id, string[] so_cau)
        {
            List<Test_QuestionModel> test_question = new List<Test_QuestionModel>();
            string sql = "";
            string sql1 = "";
            string sql2 = "";
            int tong_so_cau = 0;
            Random rnd = new Random();
            for (int i = 0; i < so_cau.Length; i++)
            {
                tong_so_cau += Convert.ToInt32(so_cau[i]);
            }
            for (int i = 0; i < tong_so_cau; i++)
            {
                sql2 += "select " + rnd.Next(1, tong_so_cau) + " ran from dual ";
                sql2 += " union all ";
            }
            sql2 = sql2.Substring(0, sql2.Length - 11);
            string current_date = DateTime.Now.ToString("dd/MM/yyyy");
            for (int i = 0; i < phanloai_id.Length; i++)
            {
                sql += " SELECT * FROM (";
                sql += " SELECT *  FROM   cau_hoi where phanloai_id=" + phanloai_id[i] + " ORDER BY DBMS_RANDOM.RANDOM";
                sql += " )WHERE  rownum <= " + so_cau[i] + " AND ngay_ket_thuc > to_date('"+current_date+"','dd/mm/yyyy') and status=1 ";
                if (phanloai_id.Length != 1 && i < phanloai_id.Length - 1)
                    sql += " UNION ";
            }
            //sql1 += " select a.*,b.de_thi_id,c.id cau_tl_id,c.name cau_tl_name,c.status trangthai_tl from";
            sql1 += " select * from";
            sql1 += " (select a.*,b.de_thi_id,c.id cau_tl_id,c.name cau_tl_name,c.status trangthai_tl";
            sql1 += " from cau_hoi a,de_thi_ct b, cau_traloi c";
            sql1 += " where a.phanloai_id=b.phanloai_id and c.cau_hoi_id=a.id and b.de_thi_id="+de_thi_id+" order by a.id)a,";
            sql1 += " (select id,b.ran from";
            sql1 += "     (select id, rownum ra from";
            sql1 += "            (select distinct a.id from";

            sql1 += " (" + sql + ")a,";
            sql1 += " (select * from de_thi_ct ) b,(select * from cau_traloi) c";
            sql1 += " where a.phanloai_id=b.phanloai_id and c.cau_hoi_id=a.id and b.de_thi_id=" + de_thi_id;// " order by a.id,DBMS_RANDOM.RANDOM";
            sql1 += ")) a,  (select rownum rb,b.* from ("+ sql2 + ") b) b  where a.ra=b.rb) b  where a.id=b.id order by ran,a.id,DBMS_RANDOM.RANDOM ";
            OracleDataReader odr = General.Lib_GetDataReader(sql1);
            int ngay_taoIndex = 0;
            int ngay_ket_thucIndex = 0;
            while (odr.Read())
            {
                ngay_taoIndex = odr.GetOrdinal("ngay_tao");
                ngay_ket_thucIndex = odr.GetOrdinal("ngay_ket_thuc");
                test_question.Add(new Test_QuestionModel
                {
                    id = odr["id"].ToString(),
                    name = odr["name"].ToString(),
                    ghi_chu = odr["ghi_chu"].ToString(),
                    status = odr["status"].ToString() == "1" ? "Bình thường" : "Khóa",
                    ngay_tao = odr.IsDBNull(ngay_taoIndex) ? null : odr.GetDateTime(ngay_taoIndex).ToString("dd/MM/yyyy"),
                    ngay_ket_thuc = odr.IsDBNull(ngay_ket_thucIndex) ? null : odr.GetDateTime(ngay_ket_thucIndex).ToString("dd/MM/yyyy"),
                    phanloai_id = odr["phanloai_id"].ToString(),
                    de_thi_id = odr["de_thi_id"].ToString(),
                    cau_tl_id = odr["cau_tl_id"].ToString(),
                    cau_tl_name = odr["cau_tl_name"].ToString(),
                    trangthai_tl = odr["trangthai_tl"].ToString()
                });
            }
            General.conn_global.Close();
            return test_question;
        }

        public List<Test_QuestionModel> getQuestionRandom_Dethi_Pattern(string de_thi_id)
        {
            List<Test_QuestionModel> test_question = new List<Test_QuestionModel>();
            string sql = "";
            string current_date = DateTime.Now.ToString("dd/MM/yyyy");
            sql+=" select a.*,b.de_thi_id,c.id cau_tl_id,c.name cau_tl_name,c.status trangthai_tl from ";
            sql += " (SELECT * FROM cau_hoi where ngay_ket_thuc > to_date('" + current_date + "','dd/mm/yyyy') and status=1)a, ";
            sql+=" (select id,de_thi_id,b.cau_hoi_id from de_thi_mau,table(cau_hoi_id) b ) b,";
            sql+=" (select * from cau_traloi) c ";
            sql += " where a.id=b.cau_hoi_id and c.cau_hoi_id=a.id and b.de_thi_id="+de_thi_id+" order by a.id,c.name";
            
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            int ngay_taoIndex = 0;
            int ngay_ket_thucIndex = 0;
            while (odr.Read())
            {
                ngay_taoIndex = odr.GetOrdinal("ngay_tao");
                ngay_ket_thucIndex = odr.GetOrdinal("ngay_ket_thuc");
                test_question.Add(new Test_QuestionModel
                {
                    id = odr["id"].ToString(),
                    name = odr["name"].ToString(),
                    ghi_chu = odr["ghi_chu"].ToString(),
                    status = odr["status"].ToString() == "1" ? "Bình thường" : "Khóa",
                    ngay_tao = odr.IsDBNull(ngay_taoIndex) ? null : odr.GetDateTime(ngay_taoIndex).ToString("dd/MM/yyyy"),
                    ngay_ket_thuc = odr.IsDBNull(ngay_ket_thucIndex) ? null : odr.GetDateTime(ngay_ket_thucIndex).ToString("dd/MM/yyyy"),
                    phanloai_id = odr["phanloai_id"].ToString(),
                    de_thi_id = odr["de_thi_id"].ToString(),
                    cau_tl_id = odr["cau_tl_id"].ToString(),
                    cau_tl_name = odr["cau_tl_name"].ToString(),
                    trangthai_tl = odr["trangthai_tl"].ToString()
                });
            }
            General.conn_global.Close();
            return test_question;
        }
    }

    public class Test_QuestionDetailModel
    {
        public string id { get; set; }
        public string de_thi_id { get; set; }
        public string phanloai_id { get; set; }
        public string so_cau { get; set; }
        public List<Test_QuestionDetailModel> getTest_QuestionDetail(string id)
        {
            List<Test_QuestionDetailModel> ts = new List<Test_QuestionDetailModel>();
            string sql = "select * from de_thi_ct where de_thi_id="+id;
            OracleDataReader odr = General.Lib_GetDataReader(sql);
            while (odr.Read())
            {
                ts.Add(new Test_QuestionDetailModel
                {
                    id = odr["id"].ToString(),
                    de_thi_id = odr["de_thi_id"].ToString(),
                    phanloai_id = odr["phanloai_id"].ToString(),
                    so_cau = odr["so_cau"].ToString()
                });
            }
            General.conn_global.Close();
            return ts;
        }
    }
}